const express = require('express');

const router = express.Router();
//导入数据库操作模块
const db = require('../db/index');

//获取列表 分页
router.get('/bookList', (req, res) => {
    //获取当前页码
    const start = +req.query.start;
    //获取每页显示的条数
    const limit = +req.query.limit;

    //拼接sql语句
    const sql = `select * from goods order by view desc limit ${start},${limit} `;
    //执行sql语句
    db.query(sql, (err, goods) => {
        if (err) {
            res.json({
                code: 500,
                message: '服务器错误'
            })
        } else {

            //获取总条数
            db.query('select count(*) as total from goods', (err, result) => {
                if (err) {
                    res.json({
                        code: 500,
                        message: '服务器错误'
                    })
                } else {
                    res.json({
                        code: 200,
                        total: result[0].total,
                        goods,
                        message: '获取成功'
                    })
                }
            })
        }
    })
})

//获取浏览量最高的 xx本图书
router.get('/goodsTop', (req, res) => {
    const { count } = req.query;
    //拼接sql语句
    const sql = `select * from goods order by view desc limit 0,${+count}`;
    //执行sql语句
    db.query(sql, (err, result) => {
        if (err) {
            res.json({
                code: 500,
                message: '服务器错误'
            })
        } else {
            res.json({
                code: 200,
                mesage: '获取成功',
                goods: result
            })
        }
    })
})

//获取图书信息
router.get('/bookInfo', (req, res) => {
    const { id } = req.query;
    //拼接sql语句
    const sql = `select * from goods where id = ${id}`;
    //执行sql语句
    db.query(sql, (err, result) => {
        if (err) {
            res.json({
                code: 500,
                message: '服务器错误'
            })
        } else {
            res.json({
                code: 200,
                mesage: '获取成功',
                goods: result[0]
            })
        }
    })
})

//浏览量增长
router.get('/viewAdd', (req, res) => {
    const id = req.query.id;
    //查询评论
    db.query(`update goods set view = view + 1 where id = ${id}`, (err, result) => {
        if (err) {
            res.json({
                code: 500,
                msg: '服务器错误'
            });
        } else {
            res.json({
                code: 200,
                msg: '成功'
            });
        }
    });
});

//搜索
router.get('/search', (req, res) => {

    let { keyword, category_id, start, limit } = req.query;
    let sql = ''
    if (category_id == 0) {
        sql = `select * from goods where title like '%${keyword}%' or descrption like '%${keyword}%' limit ${start},${limit}`
    } else {
        sql = `select * from goods where (title like '%${keyword}%' or descrption like '%${keyword}%' ) and category_id=${category_id} limit ${start},${limit}`
    }

    db.query(sql, (err, goods) => {
        if (err) {
            console.log(err);
            res.json({
                code: 500,
                message: '获取失败[1]'
            })
        } else {
            //获取总数
            if (category_id == 0) {
                sql = `select count(*) as total from goods where title like '%${keyword}%' or descrption like '%${keyword}%'`
            } else {
                sql = `select count(*) as total from goods where (title like '%${keyword}%' or descrption like '%${keyword}%') and category_id=${category_id}`
            }

            db.query(sql, (err, result) => {
                if (err) {
                    res.json({
                        code: 500,
                        msg: '获取失败[2]'
                    })
                } else {
                    res.json({
                        code: 200,
                        message: '获取成功',
                        goods,
                        total: result[0].total,
                    })
                }
            })
        }
    })
})

//根据分类获取图书
router.get('/getBookByCategory', (req, res) => {
    const { category_id, start, limit } = req.query;
    const sql = `select * from goods where category_id = '${category_id}' limit ${start},${limit}`;
    db.query(sql, (err, goods) => {
        if (err) {
            res.json({
                code: 500,
                msg: '获取失败[1]'
            })
        } else {
            //获取总数
            const sql2 = `select count(*) as total from goods where category_id = '${category_id}'`;
            db.query(sql2, (err, result) => {
                if (err) {
                    res.json({
                        code: 500,
                        msg: '获取失败[2]'
                    })
                } else {
                    res.json({
                        code: 200,
                        msg: '获取成功',
                        total: result[0].total,
                        goods
                    })
                }
            })
        }
    })
})

module.exports = router;